<?php
/**
 * excel 🔧工具类
 * @time    2020-06-02
 * @author  lxy
 * @library phpoffice/phpspreadsheet PHPExcel 的 下一个版本
 */
namespace App\Tools;

use App\Project;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class  ExcelTool implements IReadFilter{

    private $PHPExcel;

    private $WriteXLS;

    private $ImportFileSuffix = [
        'xls','xlsx','csv'
    ];

    //操作工作表
    private $ActiveSheetIndex = 0;

    //反馈信息
    private $ErrorMessage;

    //起始行
    private $StartLine = 1;

    //起始列
    private $StartColumn = 'A';

    //超链接
    private $UrlColumn = [];

    public function __construct()
    {
        $this->PHPExcel = new Spreadsheet();
    }

    /**
     * @param array $Source
     * @param array $Header
     * @param string $FileName
     * @param array $UrlColumn
     * @return bool
     * @author: lxy
     * @time: 2020/6/3 6:19 下午
     * @流程:
     */
    public function Export(array $Source ,array $Header=[],string $FileName,array $UrlColumn=[]) {
        try {
            if (empty($Source)) {
                throw new \Exception('缺少数据');
            }

            if (empty($FileName)) {
                throw new \Exception('请设置导出文件');
            }

            //判断是否为二维数组
            if ($this->isArray($Source) != 2) {
                throw new \Exception('导出数据格式不正确');
            }

            //设置操作表
            $this->PHPExcel->setActiveSheetIndex($this->ActiveSheetIndex);
            //设置默认行高
            $this->PHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(22);

            //未设置表头时
            if (empty($Header)) {
                $Header = array_keys(reset($Source));
            }
            $this->setHeader($Header);

            //文字左对齐
//            $styleArray = [
//                'alignment' => [
//                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
//                ],
//            ];

            $Count = count((reset($Source)));
            $Line = $this->StartLine + 1;
            foreach ($Source as $item) {
                $Counter = 0;
                $column = $this->StartColumn;
                $EndColumn = null;

                //自动换行
                $this->PHPExcel
                    ->getActiveSheet()
                    ->getStyle("$column")
                    ->getAlignment()
                    ->setWrapText(true);

                // 此列为超链接时
                if (!in_array($column,$UrlColumn)) {
                    while (true) {
                        //自动计算列宽度
                        $this->PHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
                        if ($Counter >= $Count) {
                            $EndColumn = $column;
                            break;
                        }
                        $this->PHPExcel
                            ->getActiveSheet()
                            ->setCellValue("$column$Line", array_shift($item));

                        $Counter++;
                        $column++;
                    }
                } else {
                    while (true) {
                        //自动计算列宽度
                        $this->PHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
                        if ($Counter >= $Count) {
                            $EndColumn = $column;
                            break;
                        }

                        $value = array_shift($item);
                        $this->PHPExcel
                            ->getActiveSheet()
                            ->setCellValue("$column$Line", $value);

                        //设置超链接
                        $this->PHPExcel
                            ->getActiveSheet()
                            ->getCell("$column$Line")
                            ->getHyperlink()->setUrl($value);

                        $Counter++;
                        $column++;
                    }
                }


                //$this->PHPExcel->getActiveSheet()->getRowDimension($Line)->setRowHeight(22);

                //设置对齐方式
//                $this->PHPExcel->getActiveSheet()
//                    ->getStyle("$this->StartColumn$Line:$EndColumn$Line")
//                    ->applyFromArray($styleArray);
                $Line++;
            }

            $this->WriteXLS = new Xlsx($this->PHPExcel);
            $this->WriteXLS->save($FileName);

            return true;

        } catch (\Exception $e) {
            $this->ErrorMessage = $e->getMessage();
            return false;
        }
    }


    /**
     * 导出专家小组信息
     * @param array $data
     * @param int $groupNumber  小组人数
     * @param string $FileName
     * @return bool
     * @author: lxy
     * @time: 2020/6/16 11:43 上午
     * @流程:
     */
    public function ExportReviewGroup(array $data,int $groupNumber,string $FileName){
        $column = [
            '小组信息',
            '小组专家',
        ];

        try {
            //合并单元格 "小组专家"
            $this->PHPExcel->getActiveSheet()->mergeCells('B1:D1');
            $this->setHeader($column);

            $Line = $this->StartLine + 1;
            foreach ($data as $val) {
                //设置单元格信息 小组id 和 小组名称
                $groupInfo = "({$val->id}) {$val->name} ".PHP_EOL."项目数量：{$val->project_count}".PHP_EOL."回避关键字：{$val->keywords}";
                $this->PHPExcel
                    ->getActiveSheet()
                    ->setCellValue("A$Line", $groupInfo);

                //自动换行
                $this->PHPExcel
                    ->getActiveSheet()
                    ->getStyle("A$Line")
                    ->getAlignment()
                    ->setWrapText(true);

                //合并小组信息 小组id 和 小组名称
                $groupEnd = $Line + $groupNumber - 1;
                $this->PHPExcel
                    ->getActiveSheet()
                    ->mergeCells("A$Line:A$groupEnd");

                // 居中
                $styleArray = [
                    'alignment' => [
                        'vertical'   => Alignment::VERTICAL_CENTER
                    ],
                ];

                $this->PHPExcel->getActiveSheet()->getStyle("A$Line")->applyFromArray($styleArray);

                //写入小组信息
                foreach ($val->reviewers as $item) {
                    $this->PHPExcel
                        ->getActiveSheet()
                        ->setCellValue("B$Line", "姓名：({$item->id}) {$item->name}".PHP_EOL."电话：{$item->tel}");

                    $this->PHPExcel
                        ->getActiveSheet()
                        ->setCellValue("C$Line", "账户：{$item->username} ".PHP_EOL."密码：{$item->password}");

                    //自动换行
                    $this->PHPExcel
                        ->getActiveSheet()
                        ->getStyle("B$Line")
                        ->getAlignment()
                        ->setWrapText(true);

                    //自动换行
                    $this->PHPExcel
                        ->getActiveSheet()
                        ->getStyle("C$Line")
                        ->getAlignment()
                        ->setWrapText(true);

                    $this->PHPExcel
                        ->getActiveSheet()
                        ->setCellValue("D$Line", "{$item->tel}");

                    $this->PHPExcel
                        ->getActiveSheet()
                        ->setCellValue("D$Line", "单位：{$item->work_org_name} ".PHP_EOL."职位：{$item->prof_title}");

                    //自动换行
                    $this->PHPExcel
                        ->getActiveSheet()
                        ->getStyle("D$Line")
                        ->getAlignment()
                        ->setWrapText(true);

                    $Line++;
                }

                $this->PHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
                $this->PHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
                $this->PHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
                $this->PHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
                $this->PHPExcel->getActiveSheet()->getRowDimension($Line)->setRowHeight(22);
            }

            $this->WriteXLS = new Xlsx($this->PHPExcel);
            $this->WriteXLS->save($FileName);

            return true;

        } catch (\Exception $e) {
            $this->ErrorMessage = $e->getMessage();
            return false;
        }
    }


    public function ExportProjectReviewScore(array $data,int $groupNumber,string $FileName){
        $columns = [
            '项目名称',
            '申报人',
            '评分状态',
            '平均分',
            '是否进入会评',
            '专家评分信息'
        ];

       try {
           $this->setHeader($columns);

           //合并表头
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("A1:A2");
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("B1:B2");
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("C1:C2");
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("D1:D2");
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("E1:E2");
           $this->PHPExcel
               ->getActiveSheet()
               ->mergeCells("F1:I1");


           //设置子表头（评分信息）
           $this->PHPExcel
               ->getActiveSheet()
               ->setCellValue("F2", '专家评分时长');
           $this->PHPExcel
               ->getActiveSheet()
               ->setCellValue("G2", '专家评分');
           $this->PHPExcel
               ->getActiveSheet()
               ->setCellValue("H2", '附件证明材料是否真实有效');
           $this->PHPExcel
               ->getActiveSheet()
               ->setCellValue("I2", '专家意见');

           $Line = 3;
           foreach ($data as $item) {
               //项目名称
               $this->PHPExcel
                   ->getActiveSheet()
                   ->setCellValue("A$Line", $item->project_name);

               //申报者
               $this->PHPExcel
                   ->getActiveSheet()
                   ->setCellValue("B$Line", $item->applicant);

               //评分状态
               $this->PHPExcel
                   ->getActiveSheet()
                   ->setCellValue("C$Line", $item->score_status_phrase);

               //平均分
               $this->PHPExcel
                   ->getActiveSheet()
                   ->setCellValue("D$Line", $item->avg_score);

               //是否进入会评
               $this->PHPExcel
                   ->getActiveSheet()
                   ->setCellValue("E$Line", Project::STATUS_MEETING_REVIEW_TO_REVIEW ? '是':'否');


               //合并项目信息
               $groupEnd = $Line + $groupNumber - 1;
               for($i='A';$i<'F';$i++) {
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->mergeCells("$i$Line:$i$groupEnd");

                   //自动计算宽度
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->getColumnDimension($i)
                       ->setAutoSize(true);
               }

               //评分信息
               foreach ($item->score_per_reviewer as $score) {
                   //秒数转化为时间 年月日时分秒
                   $time = Sec2Time($score->score_time_cost);
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->setCellValue("F$Line", $time);

                   //分数
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->setCellValue("G$Line", $score->score);

                   //附件证明材料是否真实有效
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->setCellValue("H$Line", ($score->is_evidence_material_valid == 1 ? '是':'否'));

                   //专家意见
                   $this->PHPExcel
                       ->getActiveSheet()
                       ->setCellValue("I$Line", $score->remark);

                   $Line++;
               }
           }


           $this->WriteXLS = new Xlsx($this->PHPExcel);
           $this->WriteXLS->save($FileName);
           return true;

       } catch (\Exception $e) {

           $this->ErrorMessage = $e->getMessage();
           return false;
       }
    }


    /**
     * 获取excel数据
     * @param string $File
     * @return array|bool
     * @author: lxy
     * @time: 2020/6/4 11:27 上午
     * @流程:
     */
    public function Import(string $File) {
        try {
            if (!file_exists($File)) {
                throw new \Exception('导入文件不存在');
            }

            //获取文件信息
            $fileInfo  = pathinfo($File);
            $extension = strtolower($fileInfo['extension']);
            if (!in_array($extension,$this->ImportFileSuffix)) {
                throw new \Exception('导入文件格式不正确，仅支持 '.implode(',',$this->ImportFileSuffix).' 后缀文件');
            }

            //暴力输出所有excel内容
            $PHPExcel  = IOFactory::load($File);
            return $PHPExcel->getActiveSheet()
                ->toArray(null, true, true, true);

        } catch (\Exception $e) {
            $this->ErrorMessage = $e->getMessage();
            dump($this->ErrorMessage);
            return false;
        }
    }

    public function readCell($column, $row, $worksheetName = '')
    {
        // Read rows 1 to 7 and columns A to E only
        if ($row >= 1 && $row <= 7) {
            if (in_array($column, range('A', 'E'))) {
                return true;
            }
        }

        return false;
    }


    /**
     * 设置excel表格列宽度
     * @param array $setAry
     * @author: lxy
     * @time: 2020/6/3 5:27 下午
     * 🌰 $setAry = [
     *          'A' => 20,
     *          'B' => 20,
     *          'C' => 40
     *    ]
     * @return bool|Spreadsheet
     */
    public function SetColumnWidth(array $setAry) {
        try {
            foreach ( $setAry as $key => $width ) {
                $this->PHPExcel->getActiveSheet()->getColumnDimension($key)->setWidth($width);
            }
            return $this->PHPExcel;
        } catch (\Exception $e) {
            $this->ErrorMessage = $e->getMessage();
            return false;
        }
    }


    /**
     * 设置表头
     * @param array $Header
     * @author: lxy
     * @time: 2020/6/3 8:39 下午
     * @流程:
     * @throws \Exception
     */
    private function setHeader(array $Header) {
            if (empty($Header)) {
                throw new \Exception('缺少表头信息');
            }

            $EndColumn = '';
            $Counter = 0;
            $CountHeader = count($Header);
            $column = $this->StartColumn;
            while (true) {
                if ($Counter >= $CountHeader) {
                    $EndColumn = $column;
                    break;
                }
                //$i$this->StartLine 🌰 A1|B1...
                $this->PHPExcel->getActiveSheet()->setCellValue("$column$this->StartLine", $Header[$Counter]);
                $Counter++;
                $column++;
            }

            //设置行高
            $this->PHPExcel->getActiveSheet()->getRowDimension($this->StartLine)->setRowHeight(24);

            //$HeaderSize 输出 🌰 A1:J1
            $HeaderSize = "$this->StartColumn$this->StartLine:$EndColumn$this->StartLine";

            // 列名表头文字加粗
            $this->PHPExcel->getActiveSheet()->getStyle($HeaderSize)->getFont()
                ->setBold(true)
                ->setSize(12);

            // 列表头文字居中
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical'   => Alignment::VERTICAL_CENTER
                ],
            ];
            $this->PHPExcel->getActiveSheet()->getStyle($HeaderSize)->applyFromArray($styleArray);
    }

    /**
     * 设置操作表
     * @param int $index
     * @author: lxy
     * @time: 2020/6/3 6:05 下午
     * @流程:
     */
    public function SetActiveSheetIndex(int $index) {
        $this->ActiveSheetIndex = $index;
    }

    /**
     * 获取反馈信息
     * @return mixed
     * @author: lxy
     * @time: 2020/6/3 6:05 下午
     * @流程:
     */
    public function GetMessage() :string {
        return $this->ErrorMessage;
    }

    /**
     * 设置表起始行
     * @param int $FirstLine
     * @author: lxy
     * @time: 2020/6/3 6:05 下午
     * @流程:
     */
    public function SetStartLine (int $FirstLine) {
        $this->StartLine = $FirstLine;
    }

    /**
     * 设置表起始列
     * @param string $FirstColumn
     * @author: lxy
     * @time: 2020/6/3 6:06 下午
     * @流程:
     */
    public function SetStartColumn (string $FirstColumn) {
        $this->StartColumn = strtoupper($FirstColumn);
    }




    private $ArrayNumber = 0;

    /**
     * 判断是几维数组
     * @param array $Source
     * @return int
     * @author: lxy
     * @time: 2020/6/3 6:34 下午
     * @流程:
     */
    private function isArray(array $Source) {
        if (is_array($Source)) {
            $this->ArrayNumber++;
            foreach ($Source as $val ){
                if (is_array($val)) {
                    return $this->isArray($val);
                }
                break;
            }
        }
        return $this->ArrayNumber;
    }

//    private function uploadInBrowser() {
//        header('Content-Description: File Transfer');
//        header('Content-Type: application/octet-stream');
//        header('Content-Disposition: attachment; filename=' . $this->filename);
//        header('Content-Transfer-Encoding: binary');
//        header('Expires: 0');
//        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
//        header('Pragma: public');
//        header('Content-Length: ' . filesize($this->filePath));
//        //清除缓存，否则在下载的文件里有缓存中的内容
//        ob_end_clean();
//        readfile($this->filePath);
//    }

}